package com.kelai.service.impl;


import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.kelai.callback.IdCallback;
import com.kelai.common.IRepository;
import com.kelai.common.cache.RedisBean;
import com.kelai.domain.*;
import com.kelai.repository.*;
import com.kelai.service.*;
import com.kelai.utils.DateUtils;
import com.kelai.utils.SystemTool;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * Created by Liangnan on 2018/06/05.
 */
@SuppressWarnings("unchecked")
@Service("sonyShopDayDataService")
@Slf4j
public class DefaultSonyShopDayDataService implements ISonyShopDayDataService {

    //SONY顶级ID
    private final static String ROOT_SONY_CUSTOMER = "9236b103947f4f71b261bcf958286b2c";
    //初始化的时间范围
    private final static String INIT_START_DATE = LocalDate.of(2018,1,1).format(DateTimeFormatter.ISO_LOCAL_DATE);
    private final static String INIT_END_DATE = LocalDate.of(2018,6,19).format(DateTimeFormatter.ISO_LOCAL_DATE);


    @Autowired
    ICustomerService customerService;
    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    RedisBean redisBean;
    @Resource
    private IdCallback idCallback;
    @Resource
    private IShopDayService shopDayService;
    @Resource
    private IShopWeekService shopWeekService;
    @Resource
    private IShopMonthService shopMonthService;

    @Autowired
    ISonyShopDayDataRepository sonyShopDayDataRepository;

    @Autowired
    public DefaultSonyShopDayDataService(ISonyShopDayDataRepository sonyShopDayDataRepository, IDeviceDayDataRepository deviceDayDataRepository, IDeviceRepository deviceRepository, ISaleDataRepository saleDataRepository, JdbcTemplate jdbcTemplate) {
        this.sonyShopDayDataRepository = sonyShopDayDataRepository;
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 初始化索尼客流数据
     */
    @Override
    public void initSonyShopDayData(){

        //通过索尼顶级账号查找出索尼的店铺
        List<Object[]> sonyCustomerList = (List<Object[]>) customerService.findShopsByCusId(ROOT_SONY_CUSTOMER);
        List<String> sonyShopCodeList = sonyCustomerList.stream().map(objArray -> {
            return objArray[1].toString();
        }).collect(Collectors.toList());
        //key shopID value shopCode
        Map<String, String> sonyShopMap = sonyCustomerList.stream().collect(Collectors.toMap(objArray1 -> {
                    return objArray1[0].toString();
                },
                objArray2 -> {
                    return objArray2[1].toString();
                }));
        //查找未被初始化的索尼门店
        CustomerEntity condition = new CustomerEntity();
        condition.getProperties().put("category", "3");
        condition.getProperties().put("initFlag", 0);
        condition.getProperties().put("code_in", sonyShopCodeList);
        List<CustomerEntity> uninitList = customerService.listAll(condition);
        if(!CollectionUtils.isEmpty(uninitList)){
            //每次初始化一家门店
            String shopId = uninitList.get(0).getId();
            List<String> shopIdList = Lists.newArrayList(shopId);
            Map<String, Object> conditionDay = Maps.newConcurrentMap();
            conditionDay.put("_startDate", INIT_START_DATE);
            conditionDay.put("_endDate", INIT_END_DATE);
            conditionDay.put("_shopIds",shopIdList);
            List<ShopDataDayEntity> shopDataDayEntityList = shopDayService.listAll(conditionDay);
            for (ShopDataDayEntity shopDataDayEntity: shopDataDayEntityList) {
                SonyShopDayDataEntity entity = new SonyShopDayDataEntity();
                String id = SystemTool.uuid();
                entity.setId(id);
                entity.setCode(id);
                entity.setHappenTime(shopDataDayEntity.getHappenTime());
                entity.setShopId(shopDataDayEntity.getShopId());
                entity.setShopCode(sonyShopMap.get(shopDataDayEntity.getShopId()));
                entity.setShopName(redisBean.get(shopDataDayEntity.getShopId()));
                entity.setCntToday(shopDataDayEntity.getDxin());
                entity.setCreatedDate(new Date());
                entity.setModifiedDate(new Date());
                sonyShopDayDataRepository.save(entity);
            }
            //初始化后，将该门店的初始化标识修改为已初始化(初始化标识 0未初始化 1已初始化)
            String updateInitFlagSql="update t_customer set init_flag=1 where pk_id='"+shopId+"'";
            jdbcTemplate.update(updateInitFlagSql);
        }
    }

    /**
     * 查找当前客流数据(包含当天，本周，本月)
     * @param paramDate
     * @param shopCode
     * @return
     */
    @Override
    public Map<String, Object> queryCurrentData(String paramDate, String shopCode) {

        String shopDate = paramDate;
        String currentDayStart = paramDate + DateUtils.DAY_START_TIME;
        String currentDayEnd = paramDate + DateUtils.DAY_END_TIME;
        Map<String, LocalDate> weekMap = DateUtils.getWeekByDate(shopDate);
        LocalDate day1OfWeek = weekMap.get(DateUtils.MONDAY);
        LocalDate day2OfWeek = weekMap.get(DateUtils.TUESDAY);
        LocalDate day3OfWeek = weekMap.get(DateUtils.WEDNESDAY);
        LocalDate day4OfWeek = weekMap.get(DateUtils.THURSDAY);
        LocalDate day5OfWeek = weekMap.get(DateUtils.FRIDAY);
        LocalDate day6OfWeek = weekMap.get(DateUtils.SATURDAY);
        LocalDate day7OfWeek = weekMap.get(DateUtils.SUNDAY);
        String day1OfWeekStart = day1OfWeek.toString() + DateUtils.DAY_START_TIME;
        String day1OfWeekEnd = day1OfWeek.toString() + DateUtils.DAY_END_TIME;
        String day2OfWeekStart = day2OfWeek.toString() + DateUtils.DAY_START_TIME;
        String day2OfWeekEnd = day2OfWeek.toString() + DateUtils.DAY_END_TIME;
        String day3OfWeekStart = day3OfWeek.toString() + DateUtils.DAY_START_TIME;
        String day3OfWeekEnd = day3OfWeek.toString() + DateUtils.DAY_END_TIME;
        String day4OfWeekStart = day4OfWeek.toString() + DateUtils.DAY_START_TIME;
        String day4OfWeekEnd = day4OfWeek.toString() + DateUtils.DAY_END_TIME;
        String day5OfWeekStart = day5OfWeek.toString() + DateUtils.DAY_START_TIME;
        String day5OfWeekEnd = day5OfWeek.toString() + DateUtils.DAY_END_TIME;
        String day6OfWeekStart = day6OfWeek.toString() + DateUtils.DAY_START_TIME;
        String day6OfWeekEnd = day6OfWeek.toString() + DateUtils.DAY_END_TIME;
        String day7OfWeekStart = day7OfWeek.toString() + DateUtils.DAY_START_TIME;
        String day7OfWeekEnd = day7OfWeek.toString() + DateUtils.DAY_END_TIME;

        Map<String, LocalDate> monthMap = DateUtils.getMonthByDate(paramDate);
        LocalDate firstDayOfMonth = monthMap.get(DateUtils.FIRST_DAY_OF_MONTH);
        LocalDate lastDayOfMonth = monthMap.get(DateUtils.LAST_DAY_OF_MONTH);
        String firstDayOfMonthStart = firstDayOfMonth.toString() + DateUtils.DAY_START_TIME;
        String lastDayOfMonthEnd = lastDayOfMonth.toString() + DateUtils.DAY_END_TIME;

        Map<String, LocalDate> weekOfMonthMap = DateUtils.getWeekByMonth(paramDate);
        LocalDate firstWeekStart = weekOfMonthMap.get(DateUtils.FIRST_WEEK_START);
        LocalDate firstWeekEnd = weekOfMonthMap.get(DateUtils.FIRST_WEEK_END);
        LocalDate secondWeekStart = weekOfMonthMap.get(DateUtils.SECOND_WEEK_START);
        LocalDate secondWeekEnd = weekOfMonthMap.get(DateUtils.SECOND_WEEK_END);
        LocalDate thirdWeekStart = weekOfMonthMap.get(DateUtils.THIRD_WEEK_START);
        LocalDate thirdWeekEnd = weekOfMonthMap.get(DateUtils.THIRD_WEEK_END);
        LocalDate fourthWeekStart = weekOfMonthMap.get(DateUtils.FOURTH_WEEK_START);
        LocalDate fourthWeekEnd = weekOfMonthMap.get(DateUtils.FOURTH_WEEK_END);
        LocalDate fifthWeekStart = weekOfMonthMap.get(DateUtils.FIFTH_WEEK_START);
        LocalDate fifthWeekEnd = weekOfMonthMap.get(DateUtils.FIFTH_WEEK_END);

        String firstWeekStartStr = firstWeekStart.toString() + DateUtils.DAY_START_TIME;
        String firstWeekEndStr = firstWeekEnd.toString() + DateUtils.DAY_END_TIME;
        String secondWeekStartStr = secondWeekStart + DateUtils.DAY_START_TIME;
        String secondWeekEndStr = secondWeekEnd.toString() + DateUtils.DAY_END_TIME;
        String thirdWeekStartStr = thirdWeekStart.toString() + DateUtils.DAY_START_TIME;
        String thirdWeekEndStr = thirdWeekEnd.toString() + DateUtils.DAY_END_TIME;
        String fourthWeekStartStr = fourthWeekStart.toString() + DateUtils.DAY_START_TIME;
        String fourthWeekEndStr = fourthWeekEnd.toString() + DateUtils.DAY_END_TIME;



        StringBuilder strBuilder = new StringBuilder("");
        strBuilder.append(" select max(shop_name) shopName,");
        strBuilder.append(" sum(case when happen_time >= '"+currentDayStart+"' and happen_time <= '"+currentDayEnd+"' then cnt_today else 0 end) as dayCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+day1OfWeekStart+"' and happen_time <= '"+day7OfWeekEnd+"' then cnt_today else 0 end) as weekCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+firstDayOfMonthStart+"' and happen_time <= '"+lastDayOfMonthEnd+"' then cnt_today else 0 end) as monthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+day1OfWeekStart+"' and happen_time <= '"+day1OfWeekEnd+"' then cnt_today else 0 end) as day1OfWeekCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+day2OfWeekStart+"' and happen_time <= '"+day2OfWeekEnd+"' then cnt_today else 0 end) as day2OfWeekCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+day3OfWeekStart+"' and happen_time <= '"+day3OfWeekEnd+"' then cnt_today else 0 end) as day3OfWeekCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+day4OfWeekStart+"' and happen_time <= '"+day4OfWeekEnd+"' then cnt_today else 0 end) as day4OfWeekCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+day5OfWeekStart+"' and happen_time <= '"+day5OfWeekEnd+"' then cnt_today else 0 end) as day5OfWeekCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+day6OfWeekStart+"' and happen_time <= '"+day6OfWeekEnd+"' then cnt_today else 0 end) as day6OfWeekCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+day7OfWeekStart+"' and happen_time <= '"+day7OfWeekEnd+"' then cnt_today else 0 end) as day7OfWeekCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+firstWeekStartStr+"' and happen_time <= '"+firstWeekEndStr+"' then cnt_today else 0 end) as week1OfMonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+secondWeekStartStr+"' and happen_time <= '"+secondWeekEndStr+"' then cnt_today else 0 end) as week2OfMonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+thirdWeekStartStr+"' and happen_time <= '"+thirdWeekEndStr+"' then cnt_today else 0 end) as week3OfMonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+fourthWeekStartStr+"' and happen_time <= '"+fourthWeekEndStr+"' then cnt_today else 0 end) as week4OfMonthCnt ");
        if(fifthWeekStart != null){
            String fifthWeekStartStr = fifthWeekStart.toString() + DateUtils.DAY_START_TIME;
            String fifthWeekEndStr = fifthWeekEnd.toString() + DateUtils.DAY_END_TIME;
            strBuilder.append(", sum(case when happen_time >= '"+fifthWeekStartStr+"' and happen_time <= '"+fifthWeekEndStr+"' then cnt_today else 0 end) as week5OfMonthCnt ");
        }

        strBuilder.append(" from t_sony_shop_day_data where shop_code = '"+shopCode+"'");

        List<Map<String,Object>> resultList = jdbcTemplate.queryForList(strBuilder.toString());
        return resultList.get(0);



    }


    /**
     * 查找历史客流数据（目前支持6个月）
     * @param paramDate
     * @param shopCode
     * @return
     */
    @Override
    public Map<String, Object> queryHistoryData(String paramDate, String shopCode) {

        String shopDate = paramDate;
        LocalDate oriDate = LocalDate.parse(shopDate);
        Map<String, LocalDate> historyDate = DateUtils.getHistoryByDate(shopDate);

        LocalDate history1Start = historyDate.get("history1Start");
        String history1StartStr = history1Start.toString() + DateUtils.DAY_START_TIME;
        LocalDate history1End = historyDate.get("history1End");
        String history1EndStr = history1End.toString() + DateUtils.DAY_END_TIME;
        LocalDate history1= oriDate.minusMonths(1);
        Map<String, LocalDate> weeksOfHistory1 = DateUtils.getWeekByMonth(history1.toString());
        LocalDate firstWeekStart1 = weeksOfHistory1.get(DateUtils.FIRST_WEEK_START);
        LocalDate firstWeekEnd1 = weeksOfHistory1.get(DateUtils.FIRST_WEEK_END);
        LocalDate secondWeekStart1 = weeksOfHistory1.get(DateUtils.SECOND_WEEK_START);
        LocalDate secondWeekEnd1 = weeksOfHistory1.get(DateUtils.SECOND_WEEK_END);
        LocalDate thirdWeekStart1 = weeksOfHistory1.get(DateUtils.THIRD_WEEK_START);
        LocalDate thirdWeekEnd1 = weeksOfHistory1.get(DateUtils.THIRD_WEEK_END);
        LocalDate fourthWeekStart1 = weeksOfHistory1.get(DateUtils.FOURTH_WEEK_START);
        LocalDate fourthWeekEnd1 = weeksOfHistory1.get(DateUtils.FOURTH_WEEK_END);
        LocalDate fifthWeekStart1 = weeksOfHistory1.get(DateUtils.FIFTH_WEEK_START);
        LocalDate fifthWeekEnd1 = weeksOfHistory1.get(DateUtils.FIFTH_WEEK_END);
        String firstWeekStart1Str = firstWeekStart1.toString() + DateUtils.DAY_START_TIME;
        String firstWeekEnd1Str = firstWeekEnd1.toString() + DateUtils.DAY_END_TIME;
        String secondWeekStart1Str = secondWeekStart1.toString() + DateUtils.DAY_START_TIME;
        String secondWeekEnd1Str = secondWeekEnd1.toString() + DateUtils.DAY_END_TIME;
        String thirdWeekStart1Str = thirdWeekStart1.toString() + DateUtils.DAY_START_TIME;
        String thirdWeekEnd1Str = thirdWeekEnd1.toString() + DateUtils.DAY_END_TIME;
        String fourthWeekStart1Str = fourthWeekStart1.toString() + DateUtils.DAY_START_TIME;
        String fourthWeekEnd1Str = fourthWeekEnd1.toString() + DateUtils.DAY_END_TIME;

        LocalDate history2Start = historyDate.get("history2Start");
        String history2StartStr = history2Start.toString() + DateUtils.DAY_START_TIME;
        LocalDate history2End = historyDate.get("history2End");
        String history2EndStr = history2End.toString() + DateUtils.DAY_END_TIME;
        LocalDate history2= oriDate.minusMonths(2);
        Map<String, LocalDate> weeksOfHistory2 = DateUtils.getWeekByMonth(history2.toString());
        LocalDate firstWeekStart2 = weeksOfHistory2.get(DateUtils.FIRST_WEEK_START);
        LocalDate firstWeekEnd2 = weeksOfHistory2.get(DateUtils.FIRST_WEEK_END);
        LocalDate secondWeekStart2 = weeksOfHistory2.get(DateUtils.SECOND_WEEK_START);
        LocalDate secondWeekEnd2 = weeksOfHistory2.get(DateUtils.SECOND_WEEK_END);
        LocalDate thirdWeekStart2 = weeksOfHistory2.get(DateUtils.THIRD_WEEK_START);
        LocalDate thirdWeekEnd2 = weeksOfHistory2.get(DateUtils.THIRD_WEEK_END);
        LocalDate fourthWeekStart2 = weeksOfHistory2.get(DateUtils.FOURTH_WEEK_START);
        LocalDate fourthWeekEnd2 = weeksOfHistory2.get(DateUtils.FOURTH_WEEK_END);
        LocalDate fifthWeekStart2 = weeksOfHistory2.get(DateUtils.FIFTH_WEEK_START);
        LocalDate fifthWeekEnd2 = weeksOfHistory2.get(DateUtils.FIFTH_WEEK_END);
        String firstWeekStart2Str = firstWeekStart2.toString() + DateUtils.DAY_START_TIME;
        String firstWeekEnd2Str = firstWeekEnd2.toString() + DateUtils.DAY_END_TIME;
        String secondWeekStart2Str = secondWeekStart2.toString() + DateUtils.DAY_START_TIME;
        String secondWeekEnd2Str = secondWeekEnd2.toString() + DateUtils.DAY_END_TIME;
        String thirdWeekStart2Str = thirdWeekStart2.toString() + DateUtils.DAY_START_TIME;
        String thirdWeekEnd2Str = thirdWeekEnd2.toString() + DateUtils.DAY_END_TIME;
        String fourthWeekStart2Str = fourthWeekStart2.toString() + DateUtils.DAY_START_TIME;
        String fourthWeekEnd2Str = fourthWeekEnd2.toString() + DateUtils.DAY_END_TIME;

        LocalDate history3Start = historyDate.get("history3Start");
        String history3StartStr = history3Start.toString() + DateUtils.DAY_START_TIME;
        LocalDate history3End = historyDate.get("history3End");
        String history3EndStr = history3End.toString() + DateUtils.DAY_END_TIME;
        LocalDate history3= oriDate.minusMonths(3);
        Map<String, LocalDate> weeksOfHistory3 = DateUtils.getWeekByMonth(history3.toString());
        LocalDate firstWeekStart3 = weeksOfHistory3.get(DateUtils.FIRST_WEEK_START);
        LocalDate firstWeekEnd3 = weeksOfHistory3.get(DateUtils.FIRST_WEEK_END);
        LocalDate secondWeekStart3 = weeksOfHistory3.get(DateUtils.SECOND_WEEK_START);
        LocalDate secondWeekEnd3 = weeksOfHistory3.get(DateUtils.SECOND_WEEK_END);
        LocalDate thirdWeekStart3 = weeksOfHistory3.get(DateUtils.THIRD_WEEK_START);
        LocalDate thirdWeekEnd3 = weeksOfHistory3.get(DateUtils.THIRD_WEEK_END);
        LocalDate fourthWeekStart3 = weeksOfHistory3.get(DateUtils.FOURTH_WEEK_START);
        LocalDate fourthWeekEnd3 = weeksOfHistory3.get(DateUtils.FOURTH_WEEK_END);
        LocalDate fifthWeekStart3 = weeksOfHistory3.get(DateUtils.FIFTH_WEEK_START);
        LocalDate fifthWeekEnd3 = weeksOfHistory3.get(DateUtils.FIFTH_WEEK_END);
        String firstWeekStart3Str = firstWeekStart3.toString() + DateUtils.DAY_START_TIME;
        String firstWeekEnd3Str = firstWeekEnd3.toString() + DateUtils.DAY_END_TIME;
        String secondWeekStart3Str = secondWeekStart3.toString() + DateUtils.DAY_START_TIME;
        String secondWeekEnd3Str = secondWeekEnd3.toString() + DateUtils.DAY_END_TIME;
        String thirdWeekStart3Str = thirdWeekStart3.toString() + DateUtils.DAY_START_TIME;
        String thirdWeekEnd3Str = thirdWeekEnd3.toString() + DateUtils.DAY_END_TIME;
        String fourthWeekStart3Str = fourthWeekStart3.toString() + DateUtils.DAY_START_TIME;
        String fourthWeekEnd3Str = fourthWeekEnd3.toString() + DateUtils.DAY_END_TIME;

        LocalDate history4Start = historyDate.get("history4Start");
        String history4StartStr = history4Start.toString() + DateUtils.DAY_START_TIME;
        LocalDate history4End = historyDate.get("history4End");
        String history4EndStr = history4End.toString() + DateUtils.DAY_END_TIME;
        LocalDate history4= oriDate.minusMonths(4);
        Map<String, LocalDate> weeksOfHistory4 = DateUtils.getWeekByMonth(history4.toString());
        LocalDate firstWeekStart4 = weeksOfHistory4.get(DateUtils.FIRST_WEEK_START);
        LocalDate firstWeekEnd4 = weeksOfHistory4.get(DateUtils.FIRST_WEEK_END);
        LocalDate secondWeekStart4 = weeksOfHistory4.get(DateUtils.SECOND_WEEK_START);
        LocalDate secondWeekEnd4 = weeksOfHistory4.get(DateUtils.SECOND_WEEK_END);
        LocalDate thirdWeekStart4 = weeksOfHistory4.get(DateUtils.THIRD_WEEK_START);
        LocalDate thirdWeekEnd4 = weeksOfHistory4.get(DateUtils.THIRD_WEEK_END);
        LocalDate fourthWeekStart4 = weeksOfHistory4.get(DateUtils.FOURTH_WEEK_START);
        LocalDate fourthWeekEnd4 = weeksOfHistory4.get(DateUtils.FOURTH_WEEK_END);
        LocalDate fifthWeekStart4 = weeksOfHistory4.get(DateUtils.FIFTH_WEEK_START);
        LocalDate fifthWeekEnd4 = weeksOfHistory4.get(DateUtils.FIFTH_WEEK_END);
        String firstWeekStart4Str = firstWeekStart4.toString() + DateUtils.DAY_START_TIME;
        String firstWeekEnd4Str = firstWeekEnd4.toString() + DateUtils.DAY_END_TIME;
        String secondWeekStart4Str = secondWeekStart4.toString() + DateUtils.DAY_START_TIME;
        String secondWeekEnd4Str = secondWeekEnd4.toString() + DateUtils.DAY_END_TIME;
        String thirdWeekStart4Str = thirdWeekStart4.toString() + DateUtils.DAY_START_TIME;
        String thirdWeekEnd4Str = thirdWeekEnd4.toString() + DateUtils.DAY_END_TIME;
        String fourthWeekStart4Str = fourthWeekStart4.toString() + DateUtils.DAY_START_TIME;
        String fourthWeekEnd4Str = fourthWeekEnd4.toString() + DateUtils.DAY_END_TIME;

        LocalDate history5Start = historyDate.get("history5Start");
        String history5StartStr = history5Start.toString() + DateUtils.DAY_START_TIME;
        LocalDate history5End = historyDate.get("history5End");
        String history5EndStr = history5End.toString() + DateUtils.DAY_END_TIME;
        LocalDate history5= oriDate.minusMonths(5);
        Map<String, LocalDate> weeksOfHistory5 = DateUtils.getWeekByMonth(history5.toString());
        LocalDate firstWeekStart5 = weeksOfHistory5.get(DateUtils.FIRST_WEEK_START);
        LocalDate firstWeekEnd5 = weeksOfHistory5.get(DateUtils.FIRST_WEEK_END);
        LocalDate secondWeekStart5 = weeksOfHistory5.get(DateUtils.SECOND_WEEK_START);
        LocalDate secondWeekEnd5 = weeksOfHistory5.get(DateUtils.SECOND_WEEK_END);
        LocalDate thirdWeekStart5 = weeksOfHistory5.get(DateUtils.THIRD_WEEK_START);
        LocalDate thirdWeekEnd5 = weeksOfHistory5.get(DateUtils.THIRD_WEEK_END);
        LocalDate fourthWeekStart5 = weeksOfHistory5.get(DateUtils.FOURTH_WEEK_START);
        LocalDate fourthWeekEnd5 = weeksOfHistory5.get(DateUtils.FOURTH_WEEK_END);
        LocalDate fifthWeekStart5 = weeksOfHistory5.get(DateUtils.FIFTH_WEEK_START);
        LocalDate fifthWeekEnd5 = weeksOfHistory5.get(DateUtils.FIFTH_WEEK_END);
        String firstWeekStart5Str = firstWeekStart5.toString() + DateUtils.DAY_START_TIME;
        String firstWeekEnd5Str = firstWeekEnd5.toString() + DateUtils.DAY_END_TIME;
        String secondWeekStart5Str = secondWeekStart5.toString() + DateUtils.DAY_START_TIME;
        String secondWeekEnd5Str = secondWeekEnd5.toString() + DateUtils.DAY_END_TIME;
        String thirdWeekStart5Str = thirdWeekStart5.toString() + DateUtils.DAY_START_TIME;
        String thirdWeekEnd5Str = thirdWeekEnd5.toString() + DateUtils.DAY_END_TIME;
        String fourthWeekStart5Str = fourthWeekStart5.toString() + DateUtils.DAY_START_TIME;
        String fourthWeekEnd5Str = fourthWeekEnd5.toString() + DateUtils.DAY_END_TIME;

        LocalDate history6Start = historyDate.get("history6Start");
        String history6StartStr = history6Start.toString() + DateUtils.DAY_START_TIME;
        LocalDate history6End = historyDate.get("history6End");
        String history6EndStr = history6End.toString() +  DateUtils.DAY_END_TIME;
        LocalDate history6= oriDate.minusMonths(6);
        Map<String, LocalDate> weeksOfHistory6 = DateUtils.getWeekByMonth(history6.toString());
        LocalDate firstWeekStart6 = weeksOfHistory6.get(DateUtils.FIRST_WEEK_START);
        LocalDate firstWeekEnd6 = weeksOfHistory6.get(DateUtils.FIRST_WEEK_END);
        LocalDate secondWeekStart6 = weeksOfHistory6.get(DateUtils.SECOND_WEEK_START);
        LocalDate secondWeekEnd6 = weeksOfHistory6.get(DateUtils.SECOND_WEEK_END);
        LocalDate thirdWeekStart6 = weeksOfHistory6.get(DateUtils.THIRD_WEEK_START);
        LocalDate thirdWeekEnd6 = weeksOfHistory6.get(DateUtils.THIRD_WEEK_END);
        LocalDate fourthWeekStart6 = weeksOfHistory6.get(DateUtils.FOURTH_WEEK_START);
        LocalDate fourthWeekEnd6 = weeksOfHistory6.get(DateUtils.FOURTH_WEEK_END);
        LocalDate fifthWeekStart6 = weeksOfHistory6.get(DateUtils.FIFTH_WEEK_START);
        LocalDate fifthWeekEnd6 = weeksOfHistory6.get(DateUtils.FIFTH_WEEK_END);
        String firstWeekStart6Str = firstWeekStart6.toString() + DateUtils.DAY_START_TIME;
        String firstWeekEnd6Str = firstWeekEnd6.toString() + DateUtils.DAY_END_TIME;
        String secondWeekStart6Str = secondWeekStart6.toString() + DateUtils.DAY_START_TIME;
        String secondWeekEnd6Str = secondWeekEnd6.toString() + DateUtils.DAY_END_TIME;
        String thirdWeekStart6Str = thirdWeekStart6.toString() + DateUtils.DAY_START_TIME;
        String thirdWeekEnd6Str = thirdWeekEnd6.toString() + DateUtils.DAY_END_TIME;
        String fourthWeekStart6Str = fourthWeekStart6.toString() + DateUtils.DAY_START_TIME;
        String fourthWeekEnd6Str = fourthWeekEnd6.toString() + DateUtils.DAY_END_TIME;

        StringBuilder strBuilder = new StringBuilder("");
        strBuilder.append(" select max(shop_name) shopName,");
        strBuilder.append(" sum(case when happen_time >= '"+history1StartStr+"' and happen_time <= '"+history1EndStr+"' then cnt_today else 0 end) as history1MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+firstWeekStart1Str+"' and happen_time <= '"+firstWeekEnd1Str+"' then cnt_today else 0 end) as week1OfHistory1MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+secondWeekStart1Str+"' and happen_time <= '"+secondWeekEnd1Str+"' then cnt_today else 0 end) as week2OfHistory1MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+thirdWeekStart1Str+"' and happen_time <= '"+thirdWeekEnd1Str+"' then cnt_today else 0 end) as week3OfHistory1MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+fourthWeekStart1Str+"' and happen_time <= '"+fourthWeekEnd1Str+"' then cnt_today else 0 end) as week4OfHistory1MonthCnt, ");
        if(fifthWeekStart1 != null){
            String fifthWeekStart1Str = fifthWeekStart1.toString() + DateUtils.DAY_START_TIME;
            String fifthWeekEnd1Str = fifthWeekEnd1.toString() + DateUtils.DAY_END_TIME;
            strBuilder.append(" sum(case when happen_time >= '"+fifthWeekStart1Str+"' and happen_time <= '"+fifthWeekEnd1Str+"' then cnt_today else 0 end) as week5OfHistory1MonthCnt, ");
        }
        strBuilder.append(" sum(case when happen_time >= '"+history2StartStr+"' and happen_time <= '"+history2EndStr+"' then cnt_today else 0 end) as history2MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+firstWeekStart2Str+"' and happen_time <= '"+firstWeekEnd2Str+"' then cnt_today else 0 end) as week1OfHistory2MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+secondWeekStart2Str+"' and happen_time <= '"+secondWeekEnd2Str+"' then cnt_today else 0 end) as week2OfHistory2MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+thirdWeekStart2Str+"' and happen_time <= '"+thirdWeekEnd2Str+"' then cnt_today else 0 end) as week3OfHistory2MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+fourthWeekStart2Str+"' and happen_time <= '"+fourthWeekEnd2Str+"' then cnt_today else 0 end) as week4OfHistory2MonthCnt, ");
        if(fifthWeekStart2 != null){
            String fifthWeekStart2Str = fifthWeekStart2.toString() + DateUtils.DAY_START_TIME;
            String fifthWeekEnd2Str = fifthWeekEnd2.toString() + DateUtils.DAY_END_TIME;
            strBuilder.append(" sum(case when happen_time >= '"+fifthWeekStart2Str+"' and happen_time <= '"+fifthWeekEnd2Str+"' then cnt_today else 0 end) as week5OfHistory2MonthCnt, ");
        }
        strBuilder.append(" sum(case when happen_time >= '"+history3StartStr+"' and happen_time <= '"+history3EndStr+"' then cnt_today else 0 end) as history3MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+firstWeekStart3Str+"' and happen_time <= '"+firstWeekEnd3Str+"' then cnt_today else 0 end) as week1OfHistory3MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+secondWeekStart3Str+"' and happen_time <= '"+secondWeekEnd3Str+"' then cnt_today else 0 end) as week2OfHistory3MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+thirdWeekStart3Str+"' and happen_time <= '"+thirdWeekEnd3Str+"' then cnt_today else 0 end) as week3OfHistory3MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+fourthWeekStart3Str+"' and happen_time <= '"+fourthWeekEnd3Str+"' then cnt_today else 0 end) as week4OfHistory3MonthCnt, ");
        if(fifthWeekStart3 != null){
            String fifthWeekStart3Str = fifthWeekStart3.toString() + DateUtils.DAY_START_TIME;
            String fifthWeekEnd3Str = fifthWeekEnd3.toString() + DateUtils.DAY_END_TIME;
            strBuilder.append(" sum(case when happen_time >= '"+fifthWeekStart3Str+"' and happen_time <= '"+fifthWeekEnd3Str+"' then cnt_today else 0 end) as week5OfHistory3MonthCnt, ");
        }
        strBuilder.append(" sum(case when happen_time >= '"+history4StartStr+"' and happen_time <= '"+history4EndStr+"' then cnt_today else 0 end) as history4MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+firstWeekStart4Str+"' and happen_time <= '"+firstWeekEnd4Str+"' then cnt_today else 0 end) as week1OfHistory4MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+secondWeekStart4Str+"' and happen_time <= '"+secondWeekEnd4Str+"' then cnt_today else 0 end) as week2OfHistory4MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+thirdWeekStart4Str+"' and happen_time <= '"+thirdWeekEnd4Str+"' then cnt_today else 0 end) as week3OfHistory4MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+fourthWeekStart4Str+"' and happen_time <= '"+fourthWeekEnd4Str+"' then cnt_today else 0 end) as week4OfHistory4MonthCnt, ");
        if(fifthWeekStart4 != null){
            String fifthWeekStart4Str = fifthWeekStart4.toString() + DateUtils.DAY_START_TIME;
            String fifthWeekEnd4Str = fifthWeekEnd4.toString() + DateUtils.DAY_END_TIME;
            strBuilder.append(" sum(case when happen_time >= '"+fifthWeekStart4Str+"' and happen_time <= '"+fifthWeekEnd4Str+"' then cnt_today else 0 end) as week5OfHistory4MonthCnt, ");
        }
        strBuilder.append(" sum(case when happen_time >= '"+history5StartStr+"' and happen_time <= '"+history5EndStr+"' then cnt_today else 0 end) as history5MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+firstWeekStart5Str+"' and happen_time <= '"+firstWeekEnd5Str+"' then cnt_today else 0 end) as week1OfHistory5MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+secondWeekStart5Str+"' and happen_time <= '"+secondWeekEnd5Str+"' then cnt_today else 0 end) as week2OfHistory5MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+thirdWeekStart5Str+"' and happen_time <= '"+thirdWeekEnd5Str+"' then cnt_today else 0 end) as week3OfHistory5MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+fourthWeekStart5Str+"' and happen_time <= '"+fourthWeekEnd5Str+"' then cnt_today else 0 end) as week4OfHistory5MonthCnt, ");
        if(fifthWeekStart5 != null){
            String fifthWeekStart5Str = fifthWeekStart5.toString() + DateUtils.DAY_START_TIME;
            String fifthWeekEnd5Str = fifthWeekEnd5.toString() + DateUtils.DAY_END_TIME;
            strBuilder.append(" sum(case when happen_time >= '"+fifthWeekStart5Str+"' and happen_time <= '"+fifthWeekEnd5Str+"' then cnt_today else 0 end) as week5OfHistory5MonthCnt, ");
        }
        strBuilder.append(" sum(case when happen_time >= '"+history6StartStr+"' and happen_time <= '"+history6EndStr+"' then cnt_today else 0 end) as history6MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+firstWeekStart6Str+"' and happen_time <= '"+firstWeekEnd6Str+"' then cnt_today else 0 end) as week1OfHistory6MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+secondWeekStart6Str+"' and happen_time <= '"+secondWeekEnd6Str+"' then cnt_today else 0 end) as week2OfHistory6MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+thirdWeekStart6Str+"' and happen_time <= '"+thirdWeekEnd6Str+"' then cnt_today else 0 end) as week3OfHistory6MonthCnt, ");
        strBuilder.append(" sum(case when happen_time >= '"+fourthWeekStart6Str+"' and happen_time <= '"+fourthWeekEnd6Str+"' then cnt_today else 0 end) as week4OfHistory6MonthCnt ");
        if(fifthWeekStart6 != null){
            String fifthWeekStart6Str = fifthWeekStart6.toString() + DateUtils.DAY_START_TIME;
            String fifthWeekEnd6Str = fifthWeekEnd6.toString() + DateUtils.DAY_END_TIME;
            strBuilder.append(", sum(case when happen_time >= '"+fifthWeekStart6Str+"' and happen_time <= '"+fifthWeekEnd6Str+"' then cnt_today else 0 end) as week5OfHistory6MonthCnt ");
        }

        strBuilder.append(" from t_sony_shop_day_data where shop_code = '"+shopCode+"'");

        List<Map<String,Object>> resultList = jdbcTemplate.queryForList(strBuilder.toString());
        return resultList.get(0);

    }

    /**
     * 每小时更新最新数据
     */
    @Override
    public void updateDayData(){

        List<Object[]> customerList = (List<Object[]>) customerService.findShopsByCusId(ROOT_SONY_CUSTOMER);
        List<String> shopIdList = customerList.stream().map(objArray -> {
            return objArray[0].toString();
        }).collect(Collectors.toList());
        Map<String, String> shopIdCodeMap = customerList.stream().collect(Collectors.toMap(objArray1 -> {
                    return objArray1[0].toString();
                },
                objArray2 -> {
                    return objArray2[1].toString();
                }));

        // 1 查询所有门店当天的客流数据
        LocalDate now = LocalDate.now();
        Map<String, Object> conditionDay = Maps.newConcurrentMap();
        conditionDay.put("_startDate", now.toString());
        conditionDay.put("_endDate", now.toString());
        conditionDay.put("_shopIds",shopIdList);
        List<ShopDataDayEntity> shopDataDayEntityList = shopDayService.listAll(conditionDay);

        // 2 遍历数据，没有插入，有就更新
        for (ShopDataDayEntity shopDataDayEntity : shopDataDayEntityList) {

            SonyShopDayDataEntity condition = new SonyShopDayDataEntity();
            condition.getProperties().put("shopCode", shopIdCodeMap.get(shopDataDayEntity.getShopId()));
            condition.getProperties().put("happenTime_gt",now+ " 00:00:00");
            condition.getProperties().put("happenTime_le",now + " 23:59:59");
            List<SonyShopDayDataEntity> list = listAll(condition);

            SonyShopDayDataEntity entity = null;
            if (CollectionUtils.isEmpty(list)) {//插入
                entity = new SonyShopDayDataEntity();
                String id = SystemTool.uuid();
                entity.setId(id);
                entity.setCode(id);
                entity.setHappenTime(shopDataDayEntity.getHappenTime());
                entity.setShopId(shopDataDayEntity.getShopId());
                entity.setShopCode(shopIdCodeMap.get(shopDataDayEntity.getShopId()));
                entity.setShopName(redisBean.get(shopDataDayEntity.getShopId()));
                entity.setCntToday(shopDataDayEntity.getDxin());
                entity.setCreatedDate(new Date());
                entity.setModifiedDate(new Date());
            } else {//更新
                entity = list.get(0);
                entity.setCntToday(shopDataDayEntity.getDxin());
                entity.setModifiedDate(new Date());
            }
            sonyShopDayDataRepository.save(entity);

        }

    }

    @Override
    public IRepository<SonyShopDayDataEntity> getRepository() {
        return sonyShopDayDataRepository;
    }

}
